/**
 * 
 */
package metier.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import metier.*;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author Yves
 *
 */
public class UEJDBC extends UE {
 //Attributs priv�s
    private final static String ID = "id_ue"; //Nom de l'attribut idMatiere dans la BD
    private final static String NOM = "nom_ue"; //Nom de l'attribut nom en BD
    private final static String NOMBREECTS = "Nombre_Ects"; //Nom de l'attribut coefficient en BD
    private final static String NOTEMIN = "Note_Minimale_UE";
    private final static String SEMESTRE = "SEMESTRE";
    private final static String DESCRIPTION = "description_UE"; //Nom de l'attribut description en BD
    private final static String NOMTABLE = "UE";

    //Requete SQL permettant de mettre a jour l'objet dans la base de donnees
    private final static String updateQuery = "UPDATE "+ NOMTABLE +" SET "
            + NOM + " = ?, "
            + NOMBREECTS + " = '?', "
            + NOTEMIN + " = '?', "
            + SEMESTRE + " = '?', "
            + DESCRIPTION + " = ? where " + ID + " = ?";
    //Requete SQL permettant de recuperer la liste de toutes les salles de la base de donnees
    private final static String selectQuery = "SELECT * FROM "+ NOMTABLE;
    //Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
    private final static String loadQuery = "SELECT * FROM " + NOMTABLE + " WHERE " + ID + " = ?";
    //Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
    private final static String insertQuery = "INSERT INTO " + NOMTABLE + " VALUES(?, ?, ?, ?, ?, ?)";
    //Requete SQL permettant de recuperer les informations de l'objet dans la base de donnees
    private final static String deleteQuery = "DELETE FROM " + NOMTABLE + " WHERE " + ID + " = ?";
    private final static String idQuery = "SELECT seq_UE.nextval  FROM dual";

    static String getNomTable() {
        return NOMTABLE;
    }

    static String getIdCL() {
        return ID;
    }

    static String getSemestreCL() {
        return SEMESTRE;
    }

    public UEJDBC(String nom, int nombreECTS, int noteMin, String description, Semestre semestre) {
        super(nom, nombreECTS, noteMin, description, semestre);
    }

    public UEJDBC(String nom, int nombreECTS, int noteMin) {
        super(nom, nombreECTS, noteMin);
    }

    public UEJDBC() {
    }

    /* Attributs priv�s */

    /**
     * @param id identifiant de la matiere
     */
    /* (non-Javadoc)
     * @see metier.Matiere#load(int)
     */


    protected int createId() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet rset = sta.executeQuery(idQuery);
            if (!rset.next()) {
                throw new ExceptionMetier("creating new id failed due to non generated id");
            }
            int newId = rset.getInt(1);
            rset.close();
            sta.close();
            return newId;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        }
    }

    @Override
    public void load(int id) throws ExceptionMetier {
        try {
            setId(id);
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(loadQuery);
            psta.setInt(1, getId());
            ResultSet rset = psta.executeQuery();
            if (!rset.next()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            if (rset.getInt(ID) != getId()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            setNom(rset.getString(NOM));
            Semestre sem = new SemestreJDBC();
            sem.load(rset.getInt(SEMESTRE));
            setSemestre(sem);
            setDescription(rset.getString(DESCRIPTION));
            setNombreECTS(rset.getInt(NOMBREECTS));
            setNoteMin(rset.getInt(NOTEMIN));
            rset.close();
            psta.close();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Load failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Load failed", ex);
        }

    }

    @Override
    public void delete() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement sta = con.prepareStatement(deleteQuery);
            sta.setInt(1, getId());
            sta.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        }
    }

    @Override
    public void insert() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
			PreparedStatement insertEnseignant = con.prepareStatement(insertQuery);
                        if( getId() == 0) setId(createId());
			insertEnseignant.setInt(1, this.getId());
			insertEnseignant.setInt(2, this.getSemestre().getId());
			insertEnseignant.setString(3, this.getNom());
                        insertEnseignant.setString(4, getDescription());
			insertEnseignant.setInt(5, this.getNombreECTS());
                        insertEnseignant.setInt(6,getNoteMin());
			insertEnseignant.executeQuery();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        }
    }

    @Override
    public void update() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement update = con.prepareStatement(updateQuery);
            update.setString(1, getNom());
            update.setInt(2, getNombreECTS());
            update.setInt(3, getNoteMin());
            update.setInt(4, getSemestre().getId());
            update.setString(5, getDescription());
            update.setInt(6, getId());
            int res = update.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Update failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Update failed", ex);
        }
    }
}
